Gerenciar o otimizador de consultas

O otimizador de consultas do Spanner determina a maneira mais eficiente de executar uma consulta SQL. No entanto, o plano de consulta determinado pelo otimizador pode mudar um pouco quando o próprio otimizador de consulta evolui ou quando as estatísticas do banco de dados são atualizadas. Para minimizar qualquer potencial de regressão de desempenho quando o otimizador de consulta ou as estatísticas são alteradas, o Spanner fornece as seguintes opções de consulta.

• optimizer_version: as alterações no otimizador de consulta são agrupadas e liberadas como versões do otimizador. O Spanner começa a usar a versão mais recente do otimizador como padrão pelo menos 30 dias depois do lançamento dessa versão. Você pode usar a opção de versão do otimizador de consultas para executar consultas em uma versão mais antiga do otimizador.

• optimizer_statistics_package: o Spanner atualiza o otimizador estatísticas regularmente. Novas estatísticas são disponibilizadas como um pacote. Essa opção de consulta especifica um pacote de estatísticas do otimizador de consulta a ser usado ao compilar uma consulta SQL. O pacote especificado precisa ter a coleta de lixo desativada:

GoogleSQL

 ALTER STATISTICS <package_name> SET OPTIONS (allow_gc=false)

PostgreSQL

ALTER STATISTICS spanner."" SET OPTIONS (allow_gc = true)

Este guia mostra como definir essas opções individuais em diferentes escopos no Spanner.

Listar opções do otimizador de consultas

O Spanner armazena informações sobre as versões disponíveis do otimizador e os pacotes de estatísticas que podem ser selecionados.

Versões do otimizador

A versão do otimizador de consultas é um valor inteiro, com aumento de 1 a cada atualização. A versão mais recente do otimizador de consultas é 7.

Execute a seguinte instrução SQL para retornar uma lista de todas as versões compatíveis do otimizador, com as datas de lançamento correspondentes e se essa versão é o padrão. O maior número de versão retornado é a versão compatível mais recente do otimizador.

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

Versão padrão

Por padrão, o Spanner começa a usar a versão mais recente do otimizador pelo menos 30 dias depois do lançamento dessa versão. Durante o período de mais de 30 dias entre uma nova versão e essa versão se tornar a padrão, é recomendável testar as consultas na nova versão para detectar qualquer regressão.

Para encontrar a versão padrão, execute a seguinte instrução SQL:

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

A consulta retorna uma lista de todas as versões compatíveis do otimizador. A coluna IS_DEFAULT especifica qual versão é o padrão atual.

Para ver detalhes sobre cada versão, consulte o Histórico de versões do otimizador de consultas.

Pacotes de estatísticas do otimizador

Cada novo pacote de estatísticas do otimizador criado pelo Spanner recebe um nome de pacote com garantia de ser exclusivo no banco de dados em questão.

O formato do nome do pacote é auto_{PACKAGE_TIMESTAMP}UTC. No GoogleSQL, a classe ANALYZE aciona a criação do nome do pacote de estatísticas. No PostgreSQL, a instrução ANALYZE executa essa tarefa. O formato do nome do pacote de estatísticas é analyze_{PACKAGE_TIMESTAMP}UTC, em que {PACKAGE_TIMESTAMP} é o carimbo de data/hora, no fuso horário UTC, de quando a construção das estatísticas começou. Execute a seguinte instrução SQL para retornar um lista de todos os pacotes de estatísticas do otimizador disponíveis.

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

Por padrão, o Spanner usa o pacote de estatísticas do otimizador mais recente, a menos que o banco de dados ou a consulta estejam fixados em um pacote mais antigo usando um dos métodos descritos nesta página.

Precedência de modificação de opção

Se você estiver usando um banco de dados de dialeto GoogleSQL, o Spanner oferece várias maneiras de mudar as opções do otimizador. Por exemplo, você pode definir a(s) opção(ões) para um específica ou configurar a opção na biblioteca de cliente no processo ou no nível da consulta. Quando uma opção é definida de várias maneiras, a seguinte ordem de precedência é aplicada. Selecione um link para pular para a seção correspondente deste documento.

Padrão do Spanner ← opção de banco de dados ← aplicativo cliente ← variável de ambiente ← consulta do cliente ← dica de instrução

Por exemplo, veja como interpretar a ordem de precedência ao definir a versão do otimizador de consultas:

Quando você cria um banco de dados, ele usa a versão padrão do otimizador do Spanner. A configuração da versão do otimizador usando um dos métodos listados acima tem prioridade sobre qualquer item à esquerda dela. Por exemplo, configurar o otimizador para um app usando uma variável de ambiente leva precedência sobre qualquer valor definido para o banco de dados usando a opção banco de dados. Configurar a versão do otimizador usando uma dica de instrução tem a precedência mais alta para determinada consulta, tendo precedência sobre o valor definido usando qualquer outro método.

Agora, analisaremos cada método mais detalhadamente.

Definir opções do otimizador no nível do banco de dados

Você pode definir a versão padrão do otimizador em um banco de dados usando o seguinte comando DDL ALTER DATABASE.

GoogleSQL

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  7);

PostgreSQL

ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;

Você pode definir o pacote de estatísticas de maneira semelhante, conforme mostrado no exemplo a seguir.

GoogleSQL

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_statistics_package = "auto_20191128_14_47_22UTC");

PostgreSQL

ALTER DATABASE MyDatabase
SET spanner.optimizer_statistics_package = "auto_20191128_14_47_22UTC";

Também é possível configurar mais de uma opção ao mesmo tempo, conforme mostrado no comando DDL a seguir.

GoogleSQL

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version = 7,
            optimizer_statistics_package = "auto_20191128_14_47_22UTC");

É possível executar ALTER DATABASE na CLI gcloud com o gcloud CLI databases ddl update desta forma.

GoogleSQL

gcloud spanner databases ddl update MyDatabase --instance=test-instance \
    --ddl='ALTER DATABASE MyDatabase SET OPTIONS ( optimizer_version = 7 )'

PostgreSQL

gcloud spanner databases ddl update MyDatabase --instance=test-instance \
  --ddl='ALTER DATABASE MyDatabase SET spanner.optimizer_version = 7'

Definir uma opção de banco de dados como NULL (no GoogleSQL) ou DEFAULT (no PostgreSQL) limpa-a para que o valor padrão seja usado.

Para conferir o valor atual dessas opções em um banco de dados, consulte a visualização INFORMATION_SCHEMA.DATABASE_OPTIONS do GoogleSQL ou a tabela information_schema database_options do PostgreSQL da seguinte maneira.

GoogleSQL

SELECT
  s.OPTION_NAME,
  s.OPTION_VALUE
FROM
  INFORMATION_SCHEMA.DATABASE_OPTIONS s
WHERE
  s.SCHEMA_NAME=""
  AND s.OPTION_NAME IN ('optimizer_version', 'optimizer_statistics_package')

PostgreSQL

  SELECT
    s.option_name,
    s.option_value
  FROM
    information_schema.database_options s
  WHERE
    s.schema_name='public'
    AND s.option_name IN ('optimizer_version',
      'optimizer_statistics_package')

Definir opções do otimizador com bibliotecas de cliente

Quando você interage programaticamente com o Spanner por meio do cliente há diversas maneiras de alterar as opções de consulta para suas aplicativo cliente.

É necessário usar as versões mais recentes das bibliotecas de cliente para definir as opções do otimizador.

Definir opções do otimizador para um cliente do banco de dados

Um aplicativo pode definir opções do otimizador globalmente na biblioteca de cliente, configurando a propriedade de opções de consulta conforme mostrado nos snippets de código a seguir. As configurações do otimizador são armazenadas na instância do cliente e aplicadas a todas as consultas executadas durante todo o ciclo de vida do cliente. Mesmo que as opções se apliquem no nível do banco de dados no back-end, quando as opções são definidas no nível do cliente, elas se aplicam a todos os bancos de dados conectados por meio desse cliente.

C++

namespace spanner = ::google::cloud::spanner;
spanner::Client client(
    spanner::MakeConnection(db),
    google::cloud::Options{}
        .set<spanner::QueryOptimizerVersionOption>("1")
        .set<spanner::QueryOptimizerStatisticsPackageOption>(
            "auto_20191128_14_47_22UTC"));

C#

using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class CreateConnectionWithQueryOptionsAsyncSample
{
    public class Album
    {
        public int AlbumId { get; set; }
        public int SingerId { get; set; }
        public string AlbumTitle { get; set; }
    }

    public async Task<List<Album>> CreateConnectionWithQueryOptionsAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString)
        {
            // Set query options on the connection.
            QueryOptions = QueryOptions.Empty
                .WithOptimizerVersion("1")
                // The list of available statistics packages for the database can
                // be found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
                // table.
                .WithOptimizerStatisticsPackage("latest")
        };

        var albums = new List<Album>();
        var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                SingerId = reader.GetFieldValue<int>("SingerId"),
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle")
            });
        }
        return albums;
    }
}

Go

import (
	"context"
	"fmt"
	"io"
	"time"

	"cloud.google.com/go/spanner"
	sppb "cloud.google.com/go/spanner/apiv1/spannerpb"
	"google.golang.org/api/iterator"
)

func createClientWithQueryOptions(w io.Writer, database string) error {
	ctx := context.Background()
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{
			OptimizerVersion: "1",
			// The list of available statistics packages can be found by
			// querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
			OptimizerStatisticsPackage: "latest",
		},
	}
	client, err := spanner.NewClientWithConfig(
		ctx, database, spanner.ClientConfig{QueryOptions: queryOptions},
	)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: `SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void clientWithQueryOptions(DatabaseId db) {
  SpannerOptions options =
      SpannerOptions.newBuilder()
          .setDefaultQueryOptions(
              db, QueryOptions
                  .newBuilder()
                  .setOptimizerVersion("1")
                  // The list of available statistics packages can be found by querying the
                  // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
                  .setOptimizerStatisticsPackage("latest")
                  .build())
          .build();
  Spanner spanner = options.getService();
  DatabaseClient dbClient = spanner.getDatabaseClient(db);
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(
  databaseId,
  {},
  {
    optimizerVersion: '1',
    // The list of available statistics packages can be found by querying the
    // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
    optimizerStatisticsPackage: 'latest',
  }
);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;

/**
 * Create a client with query options.
 * Example:
 * ```
 * create_client_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_client_with_query_options(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient([
        'queryOptions' => [
            'optimizerVersion' => '1',
            // Pin the statistics package used for this client instance to the
            // latest version. The list of available statistics packages can be
            // found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
            // table.
            'optimizerStatisticsPackage' => 'latest'
        ]
    ]);
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues'
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client(
    query_options={
        "optimizer_version": "1",
        "optimizer_statistics_package": "latest",
    }
)
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
    )

    for row in results:
        print("VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

query_options = {
  optimizer_version: "1",
  # The list of available statistics packages can be
  # found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
  # table.
  optimizer_statistics_package: "latest"
}

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id, query_options: query_options

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"

client.execute(sql_query).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

Definir opções do otimizador com variáveis de ambiente

Para facilitar o teste de diferentes configurações do otimizador sem precisar recompilar seu app, defina as variáveis de ambiente SPANNER_OPTIMIZER_VERSION e SPANNER_OPTIMIZER_STATISTICS_PACKAGE e execute o app, como mostra o snippet a seguir.

Linux / macOS

export SPANNER_OPTIMIZER_VERSION="7"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="7"
  set SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Os valores das opções especificadas do otimizador de consulta são lidos e armazenados na instância do cliente no momento da inicialização e se aplicam a todas as consultas executadas durante todo o ciclo de vida do cliente.

Definir opções do otimizador para uma consulta do cliente

Você pode especificar um valor para a versão do otimizador ou para a versão do pacote de estatísticas no nível da consulta no aplicativo cliente especificando uma propriedade de opções de consulta ao criar sua consulta.

C++

void QueryWithQueryOptions(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto sql = spanner::SqlStatement("SELECT SingerId, FirstName FROM Singers");
  auto opts =
      google::cloud::Options{}
          .set<spanner::QueryOptimizerVersionOption>("1")
          .set<spanner::QueryOptimizerStatisticsPackageOption>("latest");
  auto rows = client.ExecuteQuery(std::move(sql), std::move(opts));

  using RowType = std::tuple<std::int64_t, std::string>;
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "SingerId: " << std::get<0>(*row) << "\t";
    std::cout << "FirstName: " << std::get<1>(*row) << "\n";
  }
  std::cout << "Read completed for [spanner_query_with_query_options]\n";
}

C#

using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class RunCommandWithQueryOptionsAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
    }

    public async Task<List<Album>> RunCommandWithQueryOptionsAsync(string projectId, string instanceId, string databaseId)
    {
        var connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");

        cmd.QueryOptions = QueryOptions.Empty
            .WithOptimizerVersion("1")
            // The list of available statistics packages for the database can
            // be found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
            // table.
            .WithOptimizerStatisticsPackage("latest");
        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album()
            {
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                SingerId = reader.GetFieldValue<int>("SingerId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle")
            });
        }
        return albums;
    }
}

Go

import (
	"context"
	"fmt"
	"io"
	"time"

	"cloud.google.com/go/spanner"
	sppb "cloud.google.com/go/spanner/apiv1/spannerpb"
	"google.golang.org/api/iterator"
)

func queryWithQueryOptions(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: `SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{
			OptimizerVersion: "1",
			// The list of available statistics packages can be found by
			// querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
			OptimizerStatisticsPackage: "latest",
		},
	}
	iter := client.Single().QueryWithOptions(ctx, stmt, queryOptions)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void queryWithQueryOptions(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement
                  .newBuilder("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")
                  .withQueryOptions(QueryOptions
                      .newBuilder()
                      .setOptimizerVersion("1")
                      // The list of available statistics packages can be found by querying the
                      // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
                      .setOptimizerStatisticsPackage("latest")
                      .build())
                  .build())) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
  queryOptions: {
    optimizerVersion: 'latest',
    // The list of available statistics packages can be found by querying the
    // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
    optimizerStatisticsPackage: 'latest',
  },
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;

/**
 * Queries sample data using SQL with query options.
 * Example:
 * ```
 * query_data_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_query_options(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues',
        [
            'queryOptions' => [
                'optimizerVersion' => '1',
                // Pin the statistics package to the latest version just for
                // this query.
                'optimizerStatisticsPackage' => 'latest'
            ]
        ]
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues",
        query_options={
            "optimizer_version": "1",
            "optimizer_statistics_package": "latest",
        },
    )

    for row in results:
        print("VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
query_options = {
  optimizer_version: "1",
  # The list of available statistics packagebs can be
  # found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
  # table.
  optimizer_statistics_package: "latest"
}

client.execute(sql_query, query_options: query_options).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

Definir opções do otimizador para uma consulta usando uma dica de instrução

Uma dica de instrução é uma dica em uma instrução de consulta que altera a execução da consulta do comportamento padrão. Definir a dica OPTIMIZER_VERSION em uma instrução força essa consulta a ser executada usando a versão especificada do otimizador de consultas.

A dica OPTIMIZER_VERSION tem a maior prioridade de versão do otimizador. Se a dica de instrução for especificada, ela será usada independentemente de todas as outras configurações de versão do otimizador.

GoogleSQL

@{OPTIMIZER_VERSION=7} SELECT * FROM MyTable;

PostgreSQL

/*@OPTIMIZER_VERSION=7*/ SELECT * FROM MyTable;

Você também pode usar o literal latest_version para definir a versão do otimizador de uma consulta para a versão mais recente, como mostrado aqui.

GoogleSQL

@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;

PostgreSQL

/*@OPTIMIZER_VERSION=latest_version*/ SELECT * FROM MyTable;

Definir a dica OPTIMIZER_STATISTICS_PACKAGE em uma instrução força essa consulta a ser executada usando a versão especificada do pacote de estatísticas do otimizador de consultas. O pacote especificado precisa ter a coleta de lixo desativada:

GoogleSQL

ALTER STATISTICS <package_name> SET OPTIONS (allow_gc=false)

PostgreSQL

ALTER STATISTICS spanner."package_name" SET OPTIONS (allow_gc=false)

A dica OPTIMIZER_STATISTICS_PACKAGE tem a precedência mais alta de configuração do pacote otimizador. Se a dica de instrução for especificada, ela será usada independentemente de todas as outras configurações de versão do pacote do otimizador.

@{OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

Também é possível usar o literal latest para usar o pacote de estatísticas mais recente.

@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;

As duas dicas podem ser definidas em uma única instrução, conforme mostrado no exemplo a seguir.

O literal default_version define a versão do otimizador de uma consulta para a versão padrão, que pode ser diferente da versão mais recente. Consulte os detalhes em Versão padrão.

GoogleSQL

@{OPTIMIZER_VERSION=default_version, OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

PostgreSQL

/*@OPTIMIZER_VERSION=default_version, OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC*/ SELECT * FROM KeyValue;

Definir opções do otimizador ao usar o driver JDBC do Spanner

É possível substituir o valor padrão da versão do otimizador e do pacote de estatísticas, especificando opções na string de conexão JDBC, como mostrado no exemplo a seguir.

Essas opções são compatíveis apenas com as versões mais recentes do driver JDBC do Spanner.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class ConnectionWithQueryOptionsExample {

  static void connectionWithQueryOptions() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    connectionWithQueryOptions(projectId, instanceId, databaseId);
  }

  static void connectionWithQueryOptions(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String optimizerVersion = "1";
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s?optimizerVersion=%s",
            projectId, instanceId, databaseId, optimizerVersion);
    try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement()) {
      // Execute a query using the optimizer version '1'.
      try (ResultSet rs =
          statement.executeQuery(
              "SELECT SingerId, FirstName, LastName FROM Singers ORDER BY LastName")) {
        while (rs.next()) {
          System.out.printf("%d %s %s%n", rs.getLong(1), rs.getString(2), rs.getString(3));
        }
      }
      try (ResultSet rs = statement.executeQuery("SHOW VARIABLE OPTIMIZER_VERSION")) {
        while (rs.next()) {
          System.out.printf("Optimizer version: %s%n", rs.getString(1));
        }
      }
    }
  }
}

Também é possível definir a versão do otimizador de consultas usando a instrução SET OPTIMIZER_VERSION, conforme mostrado no exemplo a seguir.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class SetQueryOptionsExample {

  static void setQueryOptions() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    setQueryOptions(projectId, instanceId, databaseId);
  }

  static void setQueryOptions(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
            projectId, instanceId, databaseId);
    try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement()) {
      // Instruct the JDBC connection to use version '1' of the query optimizer.
      // NOTE: Use `SET SPANNER.OPTIMIZER_VERSION='1`` when connected to a PostgreSQL database.
      statement.execute("SET OPTIMIZER_VERSION='1'");
      // Execute a query using the latest optimizer version.
      try (ResultSet rs =
          statement.executeQuery(
              "SELECT SingerId, FirstName, LastName FROM Singers ORDER BY LastName")) {
        while (rs.next()) {
          System.out.printf("%d %s %s%n", rs.getLong(1), rs.getString(2), rs.getString(3));
        }
      }
      // NOTE: Use `SHOW SPANNER.OPTIMIZER_VERSION` when connected to a PostgreSQL database.
      try (ResultSet rs = statement.executeQuery("SHOW VARIABLE OPTIMIZER_VERSION")) {
        while (rs.next()) {
          System.out.printf("Optimizer version: %s%n", rs.getString(1));
        }
      }
    }
  }
}

Para mais detalhes sobre como usar o driver de código aberto, consulte Como usar o driver JDBC de código aberto.

Como as versões inválidas do otimizador são processadas

O Spanner é compatível com um intervalo de versões do otimizador. Esse intervalo muda ao longo do tempo quando o otimizador de consultas é atualizado. Se a versão especificado estiver fora do intervalo, a consulta falhará. Por exemplo, se você tentar executar uma consulta com a dica da instrução @{OPTIMIZER_VERSION=8}, mas o número da versão mais recente do otimizador é apenas 7, o Spanner responde com esta mensagem de erro:

Query optimizer version: 8 is not supported

Processar uma configuração de pacote de estatísticas do otimizador inválida

É possível fixar seu banco de dados ou consultar qualquer pacote de estatísticas disponível usando um dos métodos descritos anteriormente nesta página. Uma consulta falhará se um nome de pacote de estatísticas inválido for fornecido. Um pacote de estatísticas especificado por uma consulta precisa ser:

• definidos no nível do banco de dados; ou os
• marcada como ALLOW_GC=false

Determinar a versão do otimizador de consultas usada para executar uma consulta

A versão do otimizador usada para uma consulta fica visível no console do Google Cloud e na Google Cloud CLI.

Console do Google Cloud

Para conferir a versão do otimizador usada em uma consulta, execute a consulta na página Spanner Studio do console do Google Cloud e selecione a guia Explicação. Você verá uma mensagem semelhante a esta:

Versão do otimizador de consultas: 7

CLI da gcloud

Para conferir a versão usada ao executar uma consulta na CLI gcloud, defina a flag --query-mode como PROFILE, conforme mostrado no snippet a seguir.

gcloud spanner databases execute-sql MyDatabase --instance=test-instance \
    --query-mode=PROFILE --sql='SELECT * FROM MyTable'

Visualizar a versão do otimizador de consultas no Metrics Explorer

O Cloud Monitoring coleta medições para ajudar você a entender o desempenho dos seus aplicativos e serviços do sistema. Uma das métricas coletadas para o Spanner é a contagem de consultas, que mede o número de consultas em uma instância, amostradas ao longo do tempo. Embora essa métrica seja muito útil para ver consultas agrupadas por código de erro, é possível usá-la para ver qual versão do otimizador foi usada para executar cada consulta.

Use o Metrics Explorer no console do Google Cloud para visualizar a Contagem de consultas da instância do banco de dados. A Figura 1 mostra a contagem de consultas para três bancos de dados. Você pode qual versão do otimizador está sendo usada em cada banco de dados.

A tabela abaixo do gráfico dessa figura mostra que my-db-1 tentou executar uma consulta com uma versão inválida do otimizador, retornando o status Uso inadequado e resultando em uma contagem de consultas igual a zero. Os outros bancos de dados executaram consultas usando as versões 1 e 2 do otimizador, respectivamente.

Figura 1. Contagem de consultas exibidas no Metrics Explorer com consultas agrupadas pela versão do otimizador.

Para configurar um gráfico semelhante para a instância:

1. Acesse o Metrics Explorer no console do Google Cloud.
2. No campo Tipo de recurso, selecione Cloud Spanner Instance.
3. No campo Métrica, selecione Count of queries.
4. No campo Agrupar por, selecione database, optimizer_version e status.

Não mostrado neste exemplo é o caso em que uma versão diferente do otimizador está sendo usada para diferentes consultas no mesmo banco de dados. Nesse caso, o gráfico exibirá um segmento de barras para cada combinação de banco de dados e versão do otimizador.

Para saber como usar o Cloud Monitoring para monitorar o Spanner instâncias, consulte Como monitorar com o Cloud Monitoring