Como gerenciar o otimizador de consultas

O otimizador de consultas do Cloud 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 para regressão de desempenho quando o otimizador de consulta ou as estatísticas são alteradas, o Cloud Spanner fornece as opções de consulta a seguir.

  • optimizer_version: as alterações no otimizador de consulta são agrupadas e liberadas como versões do otimizador. O Cloud 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 (em inglês): o Cloud Spanner atualiza as estatísticas do otimizador 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:

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

Neste guia, mostramos como definir essas opções individuais em diferentes escopos no Cloud Spanner.

Como listar versões de opções de consulta

O Cloud 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 é 3.

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 Cloud 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 Cloud Spanner recebe um nome de pacote com garantia de ser exclusivo no banco de dados em questão.

Execute a seguinte instrução SQL para retornar uma lista de todos os pacotes de estatísticas do otimizador disponíveis. O formato de cada nome de pacote é auto_{PACKAGE_TIMESTAMP}UTC, em que {PACKAGE_TIMESTAMP} é o carimbo de data/hora, no fuso horário UTC, de quando a coleta de estatísticas foi iniciada.

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

Por padrão, o Cloud 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

O Cloud Spanner oferece várias maneiras de alterar as opções do otimizador. Por exemplo, você pode definir as opções para uma consulta específica ou configurar a opção na biblioteca de cliente no nível do processo ou da consulta. Quando uma opção é definida de várias maneiras, a seguinte ordem de precedência é aplicada. Selecione um link para ir para a seção correspondente no documento.

Padrão do Cloud 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 Cloud 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, a configuração do otimizador para um aplicativo usando uma variável de ambiente tem prioridade sobre qualquer valor definido para o banco de dados usando a opção de banco de dados. Definir a versão do otimizador por meio de uma dica de instrução tem a maior prioridade para a consulta especificada, prevalecendo sobre o valor definido com qualquer outro método.

Agora, analisaremos cada método mais detalhadamente.

Como 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.

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version = 3);

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

ALTER DATABASE MyDatabase
SET OPTIONS (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.

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

Execute ALTER DATABASE no gcloud spanner com o comando gcloud spanner databases ddl update da seguinte maneira.

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

Definir uma opção de banco de dados como NULL limpa-a para que o valor padrão seja usado.

Para ver o valor atual dessas opções para um banco de dados, consulte a visualização INFORMATION_SCHEMA.DATABASE_OPTIONS da seguinte maneira.

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')

Como definir opções do otimizador com as bibliotecas de cliente do Cloud Spanner

Quando você interage programaticamente com o Cloud Spanner por meio de bibliotecas de cliente, há várias maneiras de alterar as opções de consulta do seu aplicativo cliente.

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

Como configurar opções do otimizador para um cliente de 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),
    spanner::ClientOptions().set_query_options(
        spanner::QueryOptions()
            .set_optimizer_version("1")
            .set_optimizer_statistics_package("auto_20191128_14_47_22UTC")));

C#

var builder = new SpannerConnectionStringBuilder
{
    DataSource = $"projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
};
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(builder))
{
    // Set query options on the connection.
    connection.QueryOptions = QueryOptions.Empty
        .WithOptimizerVersion("1")
        .WithOptimizerStatisticsPackage("auto_20191128_14_47_22UTC");
    var cmd = connection.CreateSelectCommand(
        "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("SingerId : "
            + reader.GetFieldValue<string>("SingerId")
            + " AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle"));
        }
    }
}

Go


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

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
	sppb "google.golang.org/genproto/googleapis/spanner/v1"
)

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($instanceId, $databaseId)
{
    $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": "auto_20191128_14_47_22UTC"
    })
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(u"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

Como 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="3"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="3"
  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.

Como 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 = spanner::QueryOptions()
                  .set_optimizer_version("1")
                  .set_optimizer_statistics_package("latest");
  auto rows = client.ExecuteQuery(std::move(sql), std::move(opts));

  using RowType = std::tuple<std::int64_t, std::string>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    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#

var builder = new SpannerConnectionStringBuilder
{
    DataSource = $"projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
};
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(builder))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
    // Set query options just for this command.
    cmd.QueryOptions = QueryOptions.Empty
        .WithOptimizerVersion("1")
        .WithOptimizerStatisticsPackage("latest");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("SingerId : "
            + reader.GetFieldValue<string>("SingerId")
            + " AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle"));
        }
    }
}

Go


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

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
	sppb "google.golang.org/genproto/googleapis/spanner/v1"
)

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($instanceId, $databaseId)
{
    $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(u"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

Como configurar 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.

@{OPTIMIZER_VERSION=3} 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.

@{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:

`ALTER STATISTICS <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.

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

Como configurar opções do otimizador ao usar o driver JDBC

É 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 de código aberto.

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.
      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));
        }
      }
      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 Cloud Spanner é compatível com uma variedade de versões do otimizador. Essas variedade muda ao longo do tempo quando o otimizador de consultas é atualizado. Se a versão especificada estiver fora do intervalo, a consulta será reprovada no Cloud Spanner. Por exemplo, se você tentar executar uma consulta com a versão do otimizador = 100 e, supondo que exceda o valor máximo atual, você receberá o erro abaixo.

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

É 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:

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

A versão do otimizador usada para uma consulta é visível no gcloud spanner e por meio do Console do Cloud.

gcloud spanner

Para ver a versão usada ao executar uma consulta no gcloud spanner, defina a sinalização --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'

Cloud Console

Para ver a versão do otimizador usada em uma consulta, execute a consulta na visualização Banco de dados de consulta do Console do Cloud e selecione a guia Explicação. Você verá uma mensagem semelhante a esta:

Esta consulta foi executada com a versão 3 do otimizador.

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 Cloud Spanner é Contagem de consultas, que mede o número de consultas em uma instância, amostrado 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 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 ver qual versão do otimizador está sendo usada em cada banco de dados.

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

Contagem de consultas agrupadas no Metrics Explorer agrupadas por versão do otimizador de consultas

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. Navegue até o Metrics Explorer no Console do 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 suas instâncias do Cloud Spanner, consulte Como monitorar com o Cloud Monitoring.